package com.bringway.p2p.dao.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONObject;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.p2p.dao.UserdzDao;



/**
 * 
 * @author zhai
 * @version 创建时间：2015年12月16日 上午9:34:42
 * @功能：提取现金界面
 */


@Repository
public class UserdzDaoImpl  extends P2PJdbcDao implements UserdzDao {
	
	private Logger logger = Logger.getLogger(ComNewsDaoImpl.class);

	/**
	 * @author zhai
	 * UserdzDao 的实现类
	 * 获取用户对账的数据结果
	 */
	@Override
	public List<Map<String, Object>> queryUserdzList(JSONObject params) {

		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String gyh = params.optString("gyh");
		String sjhm = params.optString("sjhm");
		
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		paramMap.put("gyh", gyh);
		paramMap.put("sjhm", sjhm);
		sql.append("select ID,GYH,SJHM,DJJE,KYJE,ZHYE from t_zhfw_jbxx  where 1=1");
		if(StringUtils.isNotEmpty(sjhm)){
			sql.append(" AND SJHM like "+ "'%"+sjhm+"%'");
		}
		if(StringUtils.isNotEmpty(gyh)){
			sql.append(" AND GYH =:gyh");
		}
		
		sql.append(" order by ID desc LIMIT :START,:LIMIT");
		logger.info("queryUserdzList sql is" + sql.toString());
		logger.info("queryUserdzList paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);

	}

	/**
	 * @author zhai
	 * 获取用户对账的数据个数
	 */
//	@SuppressWarnings("deprecation")
	@Override
	public int getUserdzCount(JSONObject params) {

		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String gyh = params.optString("gyh");
		String sjhm = params.optString("sjhm");
		
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		paramMap.put("gyh", gyh);
		paramMap.put("sjhm", sjhm);
		
		sql.append("select count(*) from t_zhfw_jbxx where 1=1");
		if(StringUtils.isNotEmpty(sjhm)){
			sql.append(" AND SJHM like "+ "'%"+sjhm+"%'");
		}
		if(StringUtils.isNotEmpty(gyh)){
			sql.append(" AND GYH like "+ "'%"+gyh+"%'");
		}
		logger.info("getUserdzCount sql is" + sql.toString());
		logger.info("getUserdzCount paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);

	}

	@Override
	public List queryAllJe(String username) {
		Map<String, Object> paramMap = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer();
		sql.append("select sum(t1.TCJE) as TCJE,sum(t1.TZSY) as TZSY from t_zhfw_tzrxx t1 where 1=1 and t1.USERID=:username");
		paramMap.put("username", username);
		logger.info("queryAllInfo sql is" + sql.toString());
		logger.info("queryAllInfo paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public List queryCZJE(String username) {
		Map<String, Object> paramMap = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer();
		sql.append("select sum(t1.CZJE) as CZJE from t_zhfw_czjl_fhjg t1 left join t_zhfw_czjl t2 on t2.DDH=t1.DDH  where 1=1 and t2.GYH=:username");
		paramMap.put("username", username);
		logger.info("queryCZJE sql is" + sql.toString());
		logger.info("queryCZJE paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public Map<String, Object> queryTxSxf(String username) {
		Map<String, Object> paramMap = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer();
		sql.append("select sum(TXJE) as TXJE,sum(SXF) as SXF from t_zhfw_tx where 1=1 and GYH=:username");
		paramMap.put("username", username);
		logger.info("queryTxSxf sql is" + sql.toString());
		logger.info("queryTxSxf paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForMap(sql.toString(), paramMap);
	}

	@Override
	public Map<String, Object> queryDianJuan(String username) {
		Map<String, Object> paramMap = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer();
		sql.append("select * from (select t2.gyh GYH,ifnull(t2.DQ_JE,0) ZHSYDQE");
		sql.append(",sum(case when CHANGE_TYPE='1003' then t1.JE else 0 end) XFDQE");
		sql.append(",sum(case when CHANGE_TYPE in('1001','1002') then t1.JE else 0 end) HDDQE");
		sql.append(",(sum(case when CHANGE_TYPE in('1001','1002') then t1.JE else 0 end)-sum(case when CHANGE_TYPE='1003' then t1.JE else 0 end)-ifnull(t2.DQ_JE,0)) HDCZ");
		sql.append(" from t_zhfw_jbxx t2");
		sql.append(" left join ");
		sql.append("(select CHANGE_TYPE,ifnull(sum(OCCUR_JE),0) JE,USER_NAME from t_zhfw_dq_details_log group by CHANGE_TYPE,USER_NAME) t1 on t1.USER_NAME=t2.GYH group by t2.GYH) T");
		sql.append(" where 1=1 and T.GYH=:username");
		paramMap.put("username", username);
		logger.info("queryDianJuan sql is" + sql.toString());
		logger.info("queryDianJuan paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForMap(sql.toString(), paramMap);
	}

}
